
---------- BMDBATCHADDROW.SQL
/****** Object:  Stored Procedure dbo.BMDBatchAddRow    Script Date: 10/27/2006 2:59:26 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BMDBatchAddRow]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[BMDBatchAddRow]
create procedure dbo.BMDBatchAddRow (
INSERT INTO BMDBatch (

---------- BMDBATCHREPORT.SQL
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BMDBatchReport]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[BMDBatchReport]
*Name		 : BMDBatchReport
*Purpose	 : BMDBatchReport query.
CREATE procedure dbo.BMDBatchReport  (
    FROM BMDBatch AS b
            FROM (BMDBatch AS b  
            INNER JOIN BMDSales AS s ON b.BatchID = s.BatchID)  
            INNER JOIN RetailerRegionSales AS rrs ON s.BMDSalesID = rrs.BMDSalesID
            FROM BMDBatch AS b  
            INNER JOIN BMDSales AS s ON b.BatchID = s.BatchID
            FROM (BMDBatch AS b  
            INNER JOIN BMDSales AS s ON b.BatchID = s.BatchID)  
            LEFT JOIN RetailerRegionSales AS rrs ON s.BMDSalesID = rrs.BMDSalesID
            WHERE (((b.BatchID)=@BatchID) AND ((rrs.BMDSalesID) Is Null) 
GRANT EXEC ON dbo.BMDBatchReport TO satrack

---------- BMDBATCHUPDATEROW.SQL
/****** Object:  Stored Procedure dbo.BMDBatchUpdateRow    Script Date: 10/27/2006 2:59:26 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BMDBatchUpdateRow]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[BMDBatchUpdateRow]
create procedure dbo.BMDBatchUpdateRow (
UPDATE BMDBatch SET 

---------- BMDPRODUCTADDROW.SQL
/****** Object:  Stored Procedure dbo.BMDProductAddRow    Script Date: 10/27/2006 2:59:26 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BMDProductAddRow]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[BMDProductAddRow]
/****** Object:  Stored Procedure dbo.BMDProductAddRow    Script Date: 12/18/2002 1:53:05 PM ******/
CREATE  procedure dbo.BMDProductAddRow (
	@BMDProductDesc varchar(50),
  SELECT * FROM BMDProduct WHERE MajorMinorID = @MajorMinorID
    exec BMDProductUpdateRow @MajorMinorID, @BMDProductDesc, @ProductTypeID
    INSERT INTO BMDProduct (
	BMDProductDesc,
	@BMDProductDesc,

---------- BMDPRODUCTDELETEROW.SQL
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BMDProductDeleteRow]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[BMDProductDeleteRow]
*Name          : BMDProductDeleteRow
CREATE PROCEDURE dbo.BMDProductDeleteRow (
--     -- clean up BMDSales
--     UPDATE BMDSales 
--     WHERE BMDSales.MajorMinorID = @MajorMinorID
--     -- delete BMDProduct
--     DELETE BMDProduct 
    Do not delete the BMD Product if used as a foreign key in another table.
    IF 0 <> (SELECT COUNT(*) FROM BMDSales WHERE MajorMinorID = @MajorMinorID)
        /* Cannot physically delete the BMDProduct record. */
        UPDATE BMDProduct 
        /* OK to delete the BMDProduct record. */
        DELETE FROM BMDProduct WHERE MajorMinorID = @MajorMinorID
GRANT EXEC ON dbo.BMDProductDeleteRow TO satrack

---------- BMDPRODUCTUPDATEROW.SQL
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BMDProductUpdateRow]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[BMDProductUpdateRow]
*Name          : BMDProductUpdateRow
CREATE PROCEDURE dbo.BMDProductUpdateRow (
	@BMDProductDesc VARCHAR(50),
UPDATE BMDProduct SET 
	BMDProductDesc = @BMDProductDesc,
GRANT EXEC ON dbo.BMDProductUpdateRow TO satrack

---------- BMDSALESADDROW.SQL
/****** Object:  Stored Procedure dbo.BMDSalesAddRow    Script Date: 10/27/2006 2:59:26 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BMDSalesAddRow]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[BMDSalesAddRow]
CREATE procedure dbo.BMDSalesAddRow (
	@BMDShipToID int,
INSERT INTO dbo.BMDSales (
	BMDShipToID,
	@BMDShipToID,

---------- BMDSALESDELETEROW.SQL
/****** Object:  Stored Procedure dbo.BMDSalesDeleteRow    Script Date: 10/27/2006 2:59:26 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BMDSalesDeleteRow]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[BMDSalesDeleteRow]
CREATE procedure dbo.BMDSalesDeleteRow (
	@BMDSalesID int
UPDATE dbo.BMDSales 
WHERE BMDSalesID = @BMDSalesID

---------- BMDSALESUPDATEROW.SQL
/****** Object:  Stored Procedure dbo.BMDSalesUpdateRow    Script Date: 10/27/2006 2:59:26 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BMDSalesUpdateRow]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[BMDSalesUpdateRow]
CREATE procedure dbo.BMDSalesUpdateRow (
	@BMDSalesID int,
	@BMDShipToID int,
UPDATE dbo.BMDSales SET
	BMDShipToID = @BMDShipToID,
WHERE	BMDSalesID = @BMDSalesID

---------- BMDSHIPTOADDROW.SQL
/****** Object:  Stored Procedure dbo.BMDShipToAddRow    Script Date: 10/27/2006 2:59:26 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BMDShipToAddRow]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[BMDShipToAddRow]
/****** Object:  Stored Procedure dbo.BMDShipToAddRow    Script Date: 12/18/2002 1:53:05 PM ******/
CREATE   procedure dbo.BMDShipToAddRow (
INSERT INTO BMDShipTo (

---------- BMDSHIPTODELETEROW.SQL
/****** Object:  Stored Procedure dbo.BMDShipToDeleteRow    Script Date: 10/27/2006 2:59:26 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BMDShipToDeleteRow]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[BMDShipToDeleteRow]
CREATE procedure dbo.BMDShipToDeleteRow (
	@BMDShipToID int
UPDATE dbo.BMDShipTo 
WHERE BMDShipToID = @BMDShipToID

---------- BMDSHIPTOUPDATEROW.SQL
/****** Object:  Stored Procedure dbo.BMDShipToUpdateRow    Script Date: 10/27/2006 2:59:26 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BMDShipToUpdateRow]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[BMDShipToUpdateRow]
/****** Object:  Stored Procedure dbo.BMDShipToUpdateRow    Script Date: 12/18/2002 1:53:05 PM ******/
CREATE  procedure dbo.BMDShipToUpdateRow (
	@BMDShipToID int, 
UPDATE BMDShipTo SET
WHERE BMDShipToID = @BMDShipToID

---------- BPA_BATCHEXPORT.SQL
    DECLARE @ERR_BAD_BMD_DISTRO_ID INT = 0x80041589
    --DECLARE @BMDDistributorRetailerRegion AS TABLE
    --INSERT INTO @BMDDistributorRetailerRegion
    --VALUES('ALBUQUE', 'BMD', 'Albuquerque', 31),
    --    ('ATLANTA', 'BMD', 'Atlanta', 33),
    --    ('BALTIMOR', 'BMD', 'Baltimore', 32),
    --    ('BIDDFORD', 'BMD', 'Biddeford', 27),
    --    ('BILLINGS', 'BMD', 'Billings', 34),
    --    ('BOISE', 'BMD', 'Boise', 35),
    --    ('CHICAGO', 'BMD', 'Chicago', 50),
    --    ('DALLAS', 'BMD', 'Dallas', 36),
    --    ('DELANCO', 'BMD', 'Delanco', 37),
    --    ('DENVER', 'BMD', 'Denver', 38),
    --    ('DETROIT', 'BMD', 'Detroit', 55),
    --    ('GRANDJCT', 'BMD', 'Grand Junction', 39),
    --    ('GREENSBO', 'BMD', 'Greensboro', 40),
    --    ('HOUSTON', 'BMD', 'Houston', 41),
    --    ('IDFALLS', 'BMD', 'Idaho Falls', 42),
    --    ('LATHROP', 'BMD', 'Lathrop', 44),
    --    ('MARION', 'BMD', 'Marion', 45),
    --    ('MEMPHIS', 'BMD', 'Memphis', 46),
    --    ('MILTON', 'BMD', 'Milton', 47),
    --    ('MINNPLS', 'BMD', 'Minneapolis', 43),
    --    ('ORLANDO', 'BMD', 'Orlando', 48),
    --    ('PHOENIX', 'BMD', 'Phoenix', 49),
    --    ('PORTSMTH', 'BMD', 'Portsmouth', 28),
    --    ('RIVERSID', 'BMD', 'Riverside', 30),
    --    ('SALTLAKE', 'BMD', 'SLC', 51),
    --    ('SPOKANE', 'BMD', 'Spokane', 52),
    --    ('TULSA', 'BMD', 'Tulsa', 53),
    --    ('VANCOUVR', 'BMD', 'Vancouver', 54),
    --    ('WESTFIEL', 'BMD', 'Westfield', 29),
    --    ('WOODINVL', 'BMD', 'Woodinville', 56),
    --    ('YAKIMA', 'BMD', 'Yakima', 69)
    --RIGHT JOIN @BMDDistributorRetailerRegion AS bdrr ON bdrr.DistributorRegionID = dr.DistributorRegionID
        --,bmds.BatchID
        --,rrs.BMDSalesID
        --,bmds.Customer
        --,bmds.Warehouse
        --,bmddrr.Warehouse
    --LEFT JOIN BMDSales AS bmds ON bmds.BMDSalesID = rrs.BMDSalesID
    --INNER JOIN BMDShipTo AS st ON st.BMDShipToID = bmds.BMDShipToID
    --LEFT JOIN @BMDDistributorRetailerRegion AS bmddrr ON bmddrr.Warehouse LIKE bmds.Warehouse
    --LEFT JOIN DistributorRegion AS dr ON dr.CustomerRegion = bmds.Warehouse 
        --AND dr1.DistributorRegionLocation = bmds.Warehouse
        --AND bmds.Deleted = 0 --AND ISNULL(drr.Attributes, 0) = 0
        ,rrs.BMDSalesID
        --,bmds.Customer
        --,bmds.Warehouse
        --,bmddrr.Warehouse
        --,bmds.BatchID
    --    bmds.BatchID
    --    ,bmds.Customer
    --    ,bmds.Warehouse

---------- CLONEALLACCRUALS.SQL
    DECLARE @ERR_BAD_BMD_DISTRO_ID INT = 0x80041589

---------- CLONEALLREBATEPROGRAMS.SQL
    DECLARE @ERR_BAD_BMD_DISTRO_ID INT = 0x80041589

---------- CONSTANTSTEMPLATE.SQL
    DECLARE @ERR_BAD_BMD_DISTRO_ID INT = 0x80041589

---------- DELETEBMDSALESDISTRORETAIL.SQL
/****** Object:  Stored Procedure dbo.DeleteBMDSalesDistroRetail    Script Date: 10/27/2006 2:59:27 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DeleteBMDSalesDistroRetail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DeleteBMDSalesDistroRetail]
CREATE procedure dbo.DeleteBMDSalesDistroRetail     --(@BMDDistroID int) 
UPDATE BMDSales SET Imported = 1, ImportedDate = getDate()
WHERE BMDSales.BMDSalesID IN (
	BMDSales.BMDSalesID 
  FROM BMDSales 
	INNER JOIN BMDShipTo ON BMDSales.BMDShipToID = BMDShipTo.BMDShipToID
	INNER JOIN BMDProduct ON BMDSales.MajorMinorID = BMDProduct.MajorMinorID
        INNER JOIN DistributorRegion AS dr ON dr.CustomerRegion LIKE BMDSales.Warehouse
     	INNER JOIN DistributorRetailerRegion drr ON BMDShipTo.RetailerRegionID = drr.RetailerRegionID 
     --Drr.DistributorRegionID = @BMDDistroID and
     BMDShipTo.RetailerRegionID is not null and
     BMDProduct.ProductTypeID is not null and
     BMDSales.Imported = 0 and
     BMDSales.Deleted = 0 

---------- DISTRIBUTORLISTSUB.SQL
    --SET @List_Options = 1    -- All except BMD
    --SET @List_Options = 2    -- Only BMD
--        SELECT DISTINCT rr.DistributorRegionParentID, 0 AS BMD_Sales 
--    SELECT DISTINCT r.DistributorRegionID, 0 AS BMD_Sales 

---------- DISTRIBUTORRETAILERREGIONADDROW.SQL
    --DECLARE @ERR_BAD_BMD_DISTRO_ID INT = 0x80041589

---------- DISTRIBUTORRETAILERREGIONDELETEROW.SQL
    DECLARE @ERR_BAD_BMD_DISTRO_ID INT = 0x80041589

---------- DISTRIBUTORRETAILERREGIONMOVEROW.SQL
    --DECLARE @ERR_BAD_BMD_DISTRO_ID INT = 0x80041589

---------- FIXDISTRORETAILBUILDERID.SQL
    SELECT DISTINCT dr.DistributorRegionID, bmdst.RetailerRegionID
    FROM [dbo].[BMDSales] AS bmds
    INNER JOIN [dbo].[DistributorRegion] AS dr ON dr.CustomerRegion = bmds.Warehouse
    INNER JOIN [dbo].[BMDShipTo] AS bmdst ON bmdst.BMDShipToID = bmds.BMDShipToID
    LEFT JOIN DistributorRetailerRegion AS drr ON drr.DistributorRegionID = dr.DistributorRegionID AND drr.RetailerRegionID = bmdst.RetailerRegionID
    WHERE drr.DistroRetailID IS NULL AND dr.DistributorRegionID IS NOT NULL AND bmdst.RetailerRegionID IS NOT NULL
        RetailerRegionID = bmdst.RetailerRegionID
    --SELECT dr.DistributorRegionID, bmdst.RetailerRegionID
    INNER JOIN [dbo].[BMDSales] AS bmds ON bmds.BMDSalesID = rrs.[BMDSalesID]
    INNER JOIN [dbo].[DistributorRegion] AS dr ON dr.CustomerRegion = bmds.Warehouse
    INNER JOIN [dbo].[BMDShipTo] AS bmdst ON bmdst.BMDShipToID = bmds.BMDShipToID
    INNER JOIN [dbo].[DixieSales] AS dps ON dps.[DixieSalesID] = rrs.[BMDSalesID]

---------- GETLATESTREBATEPROGRAM.SQL
    DECLARE @ERR_BAD_BMD_DISTRO_ID INT = 0x80041589

---------- GETSIMPLEBUILDERREGIONLIST.SQL
    DECLARE @ERR_BAD_BMD_DISTRO_ID INT = 0x80041589

---------- GETSIMPLEMILLLIST.SQL
    DECLARE @ERR_BAD_BMD_DISTRO_ID INT = 0x80041589

---------- GETSIMPLERETAILERREGIONLIST.SQL
    DECLARE @ERR_BAD_BMD_DISTRO_ID INT = 0x80041589

---------- REPORTEXECBUILDERMILLACCRUALS.SQL
    DECLARE @ERR_BAD_BMD_DISTRO_ID INT = 0x80041589

---------- REPORTEXECBUILDERMILLPAYMENTS.SQL
    DECLARE @ERR_BAD_BMD_DISTRO_ID INT = 0x80041589

---------- REPORTEXECRETAILERACCRUAL.SQL
    -- SET @List_Options = 1    -- All except BMD
    -- SET @List_Options = 2    -- Only BMD

---------- REPORTEXECRETAILERACCRUAL_NEWPARENTCHILD.SQL
    -- SET @List_Options = 1    -- All except BMD
    -- SET @List_Options = 2    -- Only BMD

---------- REPORTEXECRETAILERSALES.SQL
2012-Dec-18 JP  Add BMD only option.
        -- @List_Options here is only for BMD. Never retailer list flag, e.g. stores only.
    DECLARE @ERR_BAD_BMD_DISTRO_ID INT = 0x80041589
    -- SET @List_Options = 1    -- All except BMD
    -- SET @List_Options = 2    -- Only BMD
    -- 2012-Dec-18: Add BMD only option.
    -- 2012-Dec-18: Add BMD only option.
        -- 2012-Dec-18: Add BMD only option.

---------- REPORTEXECRETAILERSALES_NEWPARENTCHILD.SQL
    -- SET @List_Options = 1    -- All except BMD
    -- SET @List_Options = 2    -- Only BMD

---------- REPORTRETAILERACCRUAL.SQL
    DECLARE @ERR_BAD_BMD_DISTRO_ID INT = 0x80041589

---------- REPORTRETAILERLISTSUB.SQL
*           : Also has the option to exclude BMD or list BMD only.
    DECLARE @ERR_BAD_BMD_DISTRO_ID INT = 0x80041589
    -- SET @List_Options = 1    -- All except BMD
    -- SET @List_Options = 2    -- Only BMD
    -- Create a list of Retailers that are BMD only 
    -- BMD only Retailers, or list all Retailers.
    DECLARE @BMD_RetailerList TABLE (
        BMD_Only INT NULL) 
    INSERT INTO @BMD_RetailerList
        CASE drr.DistributorRegionID WHEN 4 THEN 1 ELSE 0 END AS BMD_Sales 
        INSERT INTO @BMD_RetailerList
        SELECT DISTINCT rr.RetailerRegionParentID, 0 AS BMD_Sales 
        INNER JOIN @BMD_RetailerList AS t ON t.RetailerRegionID = rr.RetailerRegionID
          AND rr.RetailerRegionParentID NOT IN (SELECT DISTINCT RetailerRegionID FROM @BMD_RetailerList)
        INNER JOIN @BMD_RetailerList AS t ON t.RetailerRegionID = rr.RetailerRegionID
          AND rr.RetailerRegionParentID NOT IN (SELECT DISTINCT RetailerRegionID FROM @BMD_RetailerList)
    INSERT INTO @BMD_RetailerList
    SELECT DISTINCT r.RetailerRegionID, 0 AS BMD_Sales 
      AND r.RetailerRegionID NOT IN (SELECT DISTINCT RetailerRegionID FROM @BMD_RetailerList) 
      AND r.RetailerRegionID IN (SELECT DISTINCT RetailerRegionID FROM @BMD_RetailerList) 
          AND r.RetailerRegionID IN (SELECT DISTINCT RetailerRegionID FROM @BMD_RetailerList) 
          AND r.RetailerRegionID IN (SELECT DISTINCT RetailerRegionID FROM @BMD_RetailerList) 
          AND r.RetailerRegionID IN (SELECT DISTINCT RetailerRegionID FROM @BMD_RetailerList) 
          AND r.RetailerRegionID IN (SELECT DISTINCT RetailerRegionID FROM @BMD_RetailerList) 

---------- REPORT_EXECRETAILERACCRUAL.SQL
    DECLARE @ERR_BAD_BMD_DISTRO_ID INT = 0x80041589

---------- REPORT_EXECRETAILERSALES.SQL
2012-Dec-18 JP  Add BMD only option.
        -- 2012-Dec-18: Add BMD only option.
    DECLARE @ERR_BAD_BMD_DISTRO_ID INT = 0x80041589
    -- SET @ReportOptions = 1    -- All except BMD
    -- SET @ReportOptions = 2    -- Only BMD
    -- 2012-Dec-18: Add BMD only option.

---------- RETAILERCOPYORMOVECHILD.SQL
            -- Copy the BMD Ship To address for the new store alias.
            INSERT INTO BMDShipTo
            FROM BMDShipTo
            -- Delete the BMD Ship To address for the old store alias.
            UPDATE BMDShipTo

---------- RETAILERREBATEPSEUDOPOST.SQL
    DECLARE @ERR_BAD_BMD_DISTRO_ID INT = 0x80041589

---------- RETAILERSALESIMPORTCHECKSUMS.SQL
        -- BMD sales by ignoring DistributorRegionID 4.

---------- RETAILERSALESSUMMARYREPORT.SQL
        @BMD_Only AS INT = 0, 
    -- DECLARE @BMD_Only AS INT 
    -- SELECT @BMD_Only = -1
--AND (dr.DistributorRegionID = 4 OR dr.DistributorRegionParentID = 4 OR @BMD_Only = 0)
    SET @ParmDefinition = N'@RetailRegionID AS INT, @DistroID AS INT, @Start_Date AS VARCHAR(30), @End_Date AS VARCHAR(30), @BMD_Only AS INT, @Summary AS INT'
        @BMD_Only = @BMD_Only,

---------- UDF_OPTIONID.SQL
    DECLARE @ERR_BAD_BMD_DISTRO_ID INT = 0x80041589

---------- UDF_RETAILERREGIONDEPENDENCIES.SQL
    /* Unlink this RetailerRegionID from BMDShipTo records */
    IF 0 <> (SELECT COUNT(*) FROM BMDShipTo WHERE RetailerRegionID = @RetailerRegionID)

---------- UPDATEBMDSALESDATA.SQL
/****** Object:  Stored Procedure dbo.UpdateBMDSalesData    Script Date: 10/27/2006 2:59:29 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateBMDSalesData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateBMDSalesData]
/****** Object:  Stored Procedure dbo.UpdateBMDSalesData    Script Date: 12/18/2002 1:53:15 PM ******/
*Name		: UpdateBMDSalesData
create procedure dbo.UpdateBMDSalesData --(@BMDDistroID int) 
        FROM BMDShipTo shp
        INNER JOIN BMDSales AS ds ON ds.[BMDShipToID] = shp.[BMDShipToID]
            exec UpdateBMDShipToDistroRetail --@BMDDistroID
            exec UpdateBMDSalesDistroRetail --@BMDDistroID
            -- delete the finished rows from the BMDSales table.
            exec DeleteBMDSalesDistroRetail --@BMDDistroID

---------- UPDATEBMDSALESDISTRORETAIL.SQL
/****** Object:  Stored Procedure dbo.UpdateBMDSalesDistroRetail    Script Date: 10/27/2006 2:59:29 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateBMDSalesDistroRetail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateBMDSalesDistroRetail]
*Name		: UpdateBMDSalesDistroRetail
CREATE procedure dbo.UpdateBMDSalesDistroRetail  --(@BMDDistroID int) 
	BMDSalesID,
	BMDSales.ActivityDate,
	BMDSales.SalesAmt,
	BMDProduct.ProductTypeID,
	BMDSales.BMDSalesID,
    BMDShipTo.RetailerRegionID,
FROM BMDSales INNER JOIN BMDShipTo ON BMDSales.BMDShipToID = BMDShipTo.BMDShipToID
	      INNER JOIN BMDProduct ON BMDSales.MajorMinorID = BMDProduct.MajorMinorID
              INNER JOIN DistributorRegion AS dr ON dr.CustomerRegion LIKE BMDSales.Warehouse
     	      INNER JOIN DistributorRetailerRegion drr ON BMDShipTo.RetailerRegionID = drr.RetailerRegionID 
--Drr.DistributorRegionID = @BMDDistroID and
BMDShipTo.RetailerRegionID is not null and
BMDProduct.ProductTypeID is not null and
BMDSales.Imported = 0 and
BMDSales.Deleted = 0 

---------- UPDATEBMDSHIPTODISTRORETAIL.SQL
/****** Object:  Stored Procedure dbo.UpdateBMDShipToDistroRetail    Script Date: 10/27/2006 2:59:29 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateBMDShipToDistroRetail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateBMDShipToDistroRetail]
*Name		: UpdateBMDShipToDistroRetail
CREATE procedure dbo.UpdateBMDShipToDistroRetail --(@BMDDistroID int) 
-- identify BMDShipTos that are mapped to RetailerRegions but need
--SELECT 	distinct @BMDDistroID, shp.RetailerRegionID	
FROM BMDShipTo shp
INNER JOIN BMDSales AS bmds ON bmds.[BMDShipToID] = shp.[BMDShipToID]
INNER JOIN DistributorRegion AS dr ON dr.CustomerRegion LIKE bmds.Warehouse
	shp.BMDShipToID NOT IN (
		select BMDShipTo.BMDShipToID
		FROM BMDShipTo INNER JOIN DistributorRetailerRegion drr ON BMDShipTo.RetailerRegionID = drr.RetailerRegionID
		WHERE drr.DistributorRegionID = dr.DistributorRegionID  --@BMDDistroID

---------- _TABLES.SQL
/****** Object:  Table [dbo].[BMDBatch]    Script Date: 4/3/2014 6:21:46 AM ******/
CREATE TABLE [dbo].[BMDBatch](
 CONSTRAINT [PK_BMDBatch] PRIMARY KEY CLUSTERED 
/****** Object:  Table [dbo].[BMDProduct]    Script Date: 4/3/2014 6:21:46 AM ******/
CREATE TABLE [dbo].[BMDProduct](
	[BMDProductDesc] [varchar](50) NOT NULL,
 CONSTRAINT [BMDProduct_PK] PRIMARY KEY CLUSTERED 
/****** Object:  Table [dbo].[BMDSales]    Script Date: 4/3/2014 6:21:46 AM ******/
CREATE TABLE [dbo].[BMDSales](
	[BMDSalesID] [int] IDENTITY(1,1) NOT NULL,
	[BMDShipToID] [int] NOT NULL,
 CONSTRAINT [BMDSales_PK] PRIMARY KEY CLUSTERED 
	[BMDSalesID] ASC
/****** Object:  Table [dbo].[BMDSales_copy]    Script Date: 4/3/2014 6:21:46 AM ******/
CREATE TABLE [dbo].[BMDSales_copy](
	[BMDSalesID] [int] NOT NULL,
	[BMDShipToID] [int] NOT NULL,
/****** Object:  Table [dbo].[BMDShipTo]    Script Date: 4/3/2014 6:21:46 AM ******/
CREATE TABLE [dbo].[BMDShipTo](
	[BMDShipToID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [BMDShipTo_PK] PRIMARY KEY CLUSTERED 
	[BMDShipToID] ASC
	[BMDSalesID] [int] NULL,
ALTER TABLE [dbo].[BMDSales] ADD  CONSTRAINT [DF_BMDSales_ShipToName]  DEFAULT ('') FOR [ShipToName]
ALTER TABLE [dbo].[BMDSales] ADD  CONSTRAINT [DF_BMDSales_ShipToCity]  DEFAULT ('') FOR [ShipToCity]
ALTER TABLE [dbo].[BMDSales] ADD  CONSTRAINT [DF_BMDSales_ShipToState]  DEFAULT ('') FOR [ShipToState]
ALTER TABLE [dbo].[BMDSales] ADD  CONSTRAINT [DF_BMDSales_Imported]  DEFAULT (0) FOR [Imported]
ALTER TABLE [dbo].[BMDSales] ADD  CONSTRAINT [DF_BMDSales_Deleted]  DEFAULT (0) FOR [Deleted]
ALTER TABLE [dbo].[BMDShipTo] ADD  CONSTRAINT [DF_BMDShipTo_Deleted]  DEFAULT (0) FOR [Deleted]
ALTER TABLE [dbo].[BMDSales]  WITH CHECK ADD  CONSTRAINT [FK_BMDSales_BMDBatch] FOREIGN KEY([BatchID])
REFERENCES [dbo].[BMDBatch] ([BatchID])
ALTER TABLE [dbo].[BMDSales] CHECK CONSTRAINT [FK_BMDSales_BMDBatch]
